﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
namespace SZSLSystem.DAL
{
	/// <summary>
	///数据访问类 Room (create by HC 2013/1/14 22:28:42)
	/// </summary>
	public class Room
	{
		public Room(){}
		public bool Exists(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(OID) from Room");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			return DbHelperOleDb.Exists(strSql.ToString(), parameters);
		}

		public int Delete(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("delete from Room");
			strSql.Append(" where OID=@OID  ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			return DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
		}

		public int Add(SZSLSystem.Model.Room model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("insert into Room( ");
			strSql.Append("[OID],[WendyOID],[Modules],[Layer],[LayerHight],[RoomNumer],[VerandaState],[HouseType],[HouseTypeImage],[UnitPrice],[ConcessionaRate],[FloorArea],[InsideArea],[PublicArea],[DecorationType],[Sitting],[RoomState])");
			strSql.Append("values (@OID,@WendyOID,@Modules,@Layer,@LayerHight,@RoomNumer,@VerandaState,@HouseType,@HouseTypeImage,@UnitPrice,@ConcessionaRate,@FloorArea,@InsideArea,@PublicArea,@DecorationType,@Sitting,@RoomState)");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50),
					new SqlParameter ("@WendyOID",SqlDbType.VarChar,50),
					new SqlParameter ("@Modules",SqlDbType.Int,4),
					new SqlParameter ("@Layer",SqlDbType.Int,4),
					new SqlParameter ("@LayerHight",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomNumer",SqlDbType.VarChar,50),
					new SqlParameter ("@VerandaState",SqlDbType.VarChar,50),
					new SqlParameter ("@HouseType",SqlDbType.VarChar,50),
					new SqlParameter ("@HouseTypeImage",SqlDbType.VarChar,100),
					new SqlParameter ("@UnitPrice",SqlDbType.VarChar,50),
					new SqlParameter ("@ConcessionaRate",SqlDbType.VarChar,50),
					new SqlParameter ("@FloorArea",SqlDbType.VarChar,50),
					new SqlParameter ("@InsideArea",SqlDbType.VarChar,50),
					new SqlParameter ("@PublicArea",SqlDbType.VarChar,50),
					new SqlParameter ("@DecorationType",SqlDbType.VarChar,50),
					new SqlParameter ("@Sitting",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomState",SqlDbType.VarChar,50)};
			parameters[0].Value = model.OID;
			parameters[1].Value = model.WendyOID;
			parameters[2].Value = model.Modules;
			parameters[3].Value = model.Layer;
			parameters[4].Value = model.LayerHight;
			parameters[5].Value = model.RoomNumer;
			parameters[6].Value = model.VerandaState;
			parameters[7].Value = model.HouseType;
			parameters[8].Value = model.HouseTypeImage;
			parameters[9].Value = model.UnitPrice;
			parameters[10].Value = model.ConcessionaRate;
			parameters[11].Value = model.FloorArea;
			parameters[12].Value = model.InsideArea;
			parameters[13].Value = model.PublicArea;
			parameters[14].Value = model.DecorationType;
			parameters[15].Value = model.Sitting;
			parameters[16].Value = model.RoomState;
			object obj = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
			if (obj == null)
			{
				return 1;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}

		public  SZSLSystem.Model.Room GetModel(string OID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select top 1 [OID],[WendyOID],[Modules],[Layer],[LayerHight],[RoomNumer],[VerandaState],[HouseType],[HouseTypeImage],[UnitPrice],[ConcessionaRate],[FloorArea],[InsideArea],[PublicArea],[DecorationType],[Sitting],[RoomState] from Room");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50)};
			parameters[0].Value = OID;

			SZSLSystem.Model.Room model=new SZSLSystem.Model.Room();
			DataSet ds=DbHelperOleDb.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				DataRow row = ds.Tables[0].Rows[0];
						model.OID=row["OID"].ToString();
						model.WendyOID=row["WendyOID"].ToString();
					if (row["Modules"].ToString()!="")
					{
						model.Modules=int.Parse(row["Modules"].ToString());
					}
					if (row["Layer"].ToString()!="")
					{
						model.Layer=int.Parse(row["Layer"].ToString());
					}
						model.LayerHight=row["LayerHight"].ToString();
						model.RoomNumer=row["RoomNumer"].ToString();
						model.VerandaState=row["VerandaState"].ToString();
						model.HouseType=row["HouseType"].ToString();
						model.HouseTypeImage=row["HouseTypeImage"].ToString();
						model.UnitPrice=row["UnitPrice"].ToString();
						model.ConcessionaRate=row["ConcessionaRate"].ToString();
						model.FloorArea=row["FloorArea"].ToString();
						model.InsideArea=row["InsideArea"].ToString();
						model.PublicArea=row["PublicArea"].ToString();
						model.DecorationType=row["DecorationType"].ToString();
						model.Sitting=row["Sitting"].ToString();
						model.RoomState=row["RoomState"].ToString();

				return model;
			}
			else
			{
				return null;
			}
		}

		public int Update(SZSLSystem.Model.Room model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("update Room set ");
			strSql.Append("[OID]=@OID,");
			strSql.Append("[WendyOID]=@WendyOID,");
			strSql.Append("[Modules]=@Modules,");
			strSql.Append("[Layer]=@Layer,");
			strSql.Append("[LayerHight]=@LayerHight,");
			strSql.Append("[RoomNumer]=@RoomNumer,");
			strSql.Append("[VerandaState]=@VerandaState,");
			strSql.Append("[HouseType]=@HouseType,");
			strSql.Append("[HouseTypeImage]=@HouseTypeImage,");
			strSql.Append("[UnitPrice]=@UnitPrice,");
			strSql.Append("[ConcessionaRate]=@ConcessionaRate,");
			strSql.Append("[FloorArea]=@FloorArea,");
			strSql.Append("[InsideArea]=@InsideArea,");
			strSql.Append("[PublicArea]=@PublicArea,");
			strSql.Append("[DecorationType]=@DecorationType,");
			strSql.Append("[Sitting]=@Sitting,");
			strSql.Append("[RoomState]=@RoomState");
			strSql.Append(" where OID=@OID ");
			SqlParameter[] parameters = {
					new SqlParameter ("@OID",SqlDbType.VarChar,50),
					new SqlParameter ("@WendyOID",SqlDbType.VarChar,50),
					new SqlParameter ("@Modules",SqlDbType.Int,4),
					new SqlParameter ("@Layer",SqlDbType.Int,4),
					new SqlParameter ("@LayerHight",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomNumer",SqlDbType.VarChar,50),
					new SqlParameter ("@VerandaState",SqlDbType.VarChar,50),
					new SqlParameter ("@HouseType",SqlDbType.VarChar,50),
					new SqlParameter ("@HouseTypeImage",SqlDbType.VarChar,100),
					new SqlParameter ("@UnitPrice",SqlDbType.VarChar,50),
					new SqlParameter ("@ConcessionaRate",SqlDbType.VarChar,50),
					new SqlParameter ("@FloorArea",SqlDbType.VarChar,50),
					new SqlParameter ("@InsideArea",SqlDbType.VarChar,50),
					new SqlParameter ("@PublicArea",SqlDbType.VarChar,50),
					new SqlParameter ("@DecorationType",SqlDbType.VarChar,50),
					new SqlParameter ("@Sitting",SqlDbType.VarChar,50),
					new SqlParameter ("@RoomState",SqlDbType.VarChar,50)};
			parameters[0].Value = model.OID;
			parameters[1].Value = model.WendyOID;
			parameters[2].Value = model.Modules;
			parameters[3].Value = model.Layer;
			parameters[4].Value = model.LayerHight;
			parameters[5].Value = model.RoomNumer;
			parameters[6].Value = model.VerandaState;
			parameters[7].Value = model.HouseType;
			parameters[8].Value = model.HouseTypeImage;
			parameters[9].Value = model.UnitPrice;
			parameters[10].Value = model.ConcessionaRate;
			parameters[11].Value = model.FloorArea;
			parameters[12].Value = model.InsideArea;
			parameters[13].Value = model.PublicArea;
			parameters[14].Value = model.DecorationType;
			parameters[15].Value = model.Sitting;
			parameters[16].Value = model.RoomState;

			return DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
		}

		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select [OID],[WendyOID],[Modules],[Layer],[LayerHight],[RoomNumer],[VerandaState],[HouseType],[HouseTypeImage],[UnitPrice],[ConcessionaRate],[FloorArea],[InsideArea],[PublicArea],[DecorationType],[Sitting],[RoomState]");
			strSql.Append(" FROM Room ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public DataSet GetList(int top,string strWhere,string fieldOrder)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select");
			if(top>0)
			{
				strSql.Append(" top "+top.ToString());
			}
			strSql.Append(" [OID],[WendyOID],[Modules],[Layer],[LayerHight],[RoomNumer],[VerandaState],[HouseType],[HouseTypeImage],[UnitPrice],[ConcessionaRate],[FloorArea],[InsideArea],[PublicArea],[DecorationType],[Sitting],[RoomState]");
			strSql.Append(" FROM Room ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
				strSql.Append(" order by "+fieldOrder);
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public DataSet GetList(string showFields,string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
				strSql.Append(" select "+showFields);
			strSql.Append(" FROM Room ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.Query(strSql.ToString());
		}

		public object GetSingle(string fieldName,string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
				strSql.Append(" select "+fieldName);
			strSql.Append(" FROM Room ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperOleDb.GetSingle(strSql.ToString());
		}

	}
}